import sys,os,getopt
import time
from enum import Enum
import openpyxl
import pandas as pd
import random
import sqlite3
import colorParser as cp
import listingUtil as util
from parseSKUCode import parseImageFileName


g_basedir = '' # 数据文件夹名称
g_brand = '' # 为哪个品牌名生成资料表
g_country = '' # 为哪个国家的店铺生成资料表
opts, args = getopt.getopt(sys.argv[1:], 'd:b:c:')
if len(opts)!=3:
    print(f"Usage：脚本 -d <数据目录> -b <品牌名称> -c <区域店铺代码>\n")
    exit()
else:
    for i in opts:
        if i[0]=='-d':
            g_basedir = i[1]
        elif i[0]=='-b':
            g_brand = i[1]
        elif i[0]=='-c':
            g_country = i[1]
        else:
            print(f'无法解析的参数:{i[0]} {i[1]}')

if not os.path.isdir(g_basedir):
    print(f"basedir不是文件夹. dir={g_basedir}")
    exit()
g_brand = g_brand.upper()
g_country = g_country.upper()

# sqlite数据库文件路径
DB_PATH = 'db/data.db'

# ========================================================================常量定义 begin==
# 以spu为粒度的文本类型字段名称字典
SPU_TEXT_COL_DICT = {
    'spuKeyFeatures' : 'KeyFeatures', 'spuKeyFeatures1' : 'KeyFeatures1', 'spuKeyFeatures2' : 'KeyFeatures2',
    'spuClothingSizeGroup' : 'ClothingSizeGroup', 'spuClothingStyle' : 'ClothingStyle', 'spuFabricMaterialName' : 'FabricMaterialName', 
    'spuFabricMaterialName1' : 'FabricMaterialName1', 'spuFabricMaterialName2' : 'FabricMaterialName2',
    'spuMaterial' : 'Material', 'spuPattern' : 'Pattern', 'spuClothingCut' : 'ClothingCut', 'spuClothingFit' : 'ClothingFit',
    'spuClothingTopStyle' : 'ClothingTopStyle', 'spuClothingLengthStyle' : 'ClothingLengthStyle', 'spuClothingNeckStyle' : 'ClothingNeckStyle',
    'spuSleeveLengthStyle' : 'SleeveLengthStyle', 'spuSleeveStyle' : 'SleeveStyle', 'spuCollarStyle' : 'CollarStyle', 'spuTshirtType' : 'TshirtType', 'spuJacketStyle' : 'JacketStyle',
    'spuSweaterStyle' : 'SweaterStyle', 'spuBraStyle' : 'BraStyle', 'spuJeanStyle' : 'JeanStyle', 'spuShortsStyle' : 'ShortsStyle', 'spuSkirtStyle' : 'SkirtStyle',
    'spuSkirtLengthStyle' : 'SkirtLengthStyle', 'spuSwimsuitStyle' : 'SwimsuitStyle', 'spuDressStyle' : 'DressStyle', 'spuPajamaType' : 'PajamaType'
}
# 以spu为粒度的数值类型字段名称字典
SPU_NUMBER_COL_DICT = {
    'spuSellingPrice' : 'SellingPrice', 'spuShippingWeight' : 'ShippingWeight', 'spuFabricMaterialPercentage' : 'FabricMaterialPercentage',
    'spuFabricMaterialPercentage1' : 'FabricMaterialPercentage1', 'spuFabricMaterialPercentage2' : 'FabricMaterialPercentage2', 
}


# ========================================================================函数定义 begin==
# 初始化各字段缓存字典
def initCacheDictOfCols(ext):
    for k,v in SPU_TEXT_COL_DICT.items():
        ext[k] = {}
    for k,v in SPU_NUMBER_COL_DICT.items():
        ext[k] = {}
# 从DataFrame中取各字段值
def getValueFromDF(ext, row_of_df, spu):
    for k,v in SPU_TEXT_COL_DICT.items():
        val = str(getattr(row_of_df, v)).title()
        val = val if val!='Nan' else ''
        val = val if val!="Women'S Plus" else "Women's Plus"
        ext[k][spu] = val
    for k,v in SPU_NUMBER_COL_DICT.items():
        val = float(getattr(row_of_df, v))
        val = val if val!=0 else ''
        ext[k][spu] = val

# 异化features、occasions
# 参数 itemname_copywriting string类型; features list类型; occasions list类型
# 返回值 feature_str,occasion_str
# 逻辑：当item name的关键文案相同时，则feature、occasions至少要有1处不同
def alienateFeaturesAndOccasions(itemname_copywriting, features, occasions):
    max_times = 200 # 从数据库校验唯一性时，最大尝试次数
    feature_str = ' '.join(util.resizeAndShuffle(features))
    occasion_str = ' '.join(util.resizeAndShuffle(occasions))
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    sql = "SELECT * FROM ITEMCOPYWRITING WHERE title='{}' AND feature='{}' AND occasion='{}'"
    res = c.execute(sql.format(itemname_copywriting, feature_str, occasion_str))
    unique = not res
    count = 1
    while not unique and count<max_times:
        feature_str = ','.join(util.resizeAndShuffle(features))
        occasion_str = '/'.join(util.resizeAndShuffle(occasions))
        sql = sql.format(itemname_copywriting, feature_str, occasion_str)
        res = list(c.execute(sql))
        unique = True if len(res)==0 else False
        #print(f'[Site Description]res:{unique},sql:{sql}')
        count = count + 1
    # 如果次数小于max_times就结束，则说明此次生成的feature,occasion与itemname copywriting同时考虑时，没有重复值，则存入DB
    #print(f'[Site Description]try_times:{count}')
    if count<max_times:
        sql = f"INSERT INTO ITEMCOPYWRITING(title, feature, occasion) VALUES('{itemname_copywriting}', '{feature_str}', '{occasion_str}')"
        c.execute(sql)
        print(f'[Add ItemName]{sql}')
    conn.commit()
    conn.close()
    return feature_str, occasion_str

# 异化Site Description文案
# 参数 itemname 字符串; itemname_copywriting ItemName中的关键词部分，用于唯一性校验; material 材质成分，字符串; size 尺码描述，字符串; pattern 图案，字符串; features list; occasions list
# 返回值 整理拼接好的字符串
def alienateSitedesc(itemname, itemname_copywriting, material, size, pattern, features, occasions):
    template = '''{}<br/>
<br/>
Material: {}<br/>
Size: {}<br/>
Pattern: {}<br/>
Feature: {}<br/>
Occasion: {}'''
    features_str,occasions_str = alienateFeaturesAndOccasions(itemname_copywriting, features, occasions)
    return template.format(itemname, material, size, pattern, features_str, occasions_str)

# ========================================================================函数定义 end ==


# ======================= Test 区域 begin ==

# ======================= Test 区域 end ==


''' 读取源数据
'''
src_path = f'{g_basedir}/data-src.xls'
src_xls = pd.ExcelFile(src_path)
df_src = src_xls.parse(sheet_name='item')
cp.init(src_xls.parse(sheet_name='colorcode'))   # 初始化颜色代码解析模块

# 先把材质成分比例数据的NaN值转换成0
df_src['FabricMaterialPercentage'].fillna(0,inplace=True)
df_src['FabricMaterialPercentage1'].fillna(0,inplace=True)
df_src['FabricMaterialPercentage2'].fillna(0,inplace=True)

# 输出
out_path = f'{g_basedir}/tempData.xlsx'

g_srcdata_table = {}
g_srcdata_table['SPUs'] = []
g_srcdata_table['allColorCodeList'] = []
g_srcdata_table['allSPUList'] = []
g_srcdata_table['allSkuList'] = []
g_srcdata_table['allSizeList'] = []
g_srcdata_table['allColorList'] = []
g_srcdata_table['allColorCateList'] = []
g_srcdata_table['allIsPrimary'] = []

g_srcdata_table['spuClothingType'] = {}
g_srcdata_table['spuItemNameDic'] = {}
g_srcdata_table['spuSiteDescription'] = {}
# 初始化缓存字典
initCacheDictOfCols(g_srcdata_table)

for row in df_src.itertuples():
    LocalSPU = getattr(row, 'SPU')
    SPU = f'{g_country}{util.BRAND_TO_STORE_CODE[g_brand]}|{LocalSPU}'
    g_srcdata_table['SPUs'].append(SPU)   # 1个SPU只记录1次
    # print(SPU)
    CorewordStr = getattr(row, 'CoreWords')
    AdornwordStr = getattr(row, 'AdornWords')
    g_srcdata_table['spuClothingType'][SPU] = str(getattr(row, 'ClothingType')).title()
    ColorStr = getattr(row, 'Colors')
    SizeStr = getattr(row, 'Sizes')
    
    getValueFromDF(g_srcdata_table, row, SPU)

    # 静态附加字段
    OccasionStr = getattr(row, 'Occasions')

    # === 组装Item Name、Site Description文案 ==
    sizeGroup = str(g_srcdata_table['spuClothingSizeGroup'][SPU]).title()
    if sizeGroup=="Women'S Plus":
        sizeGroup='Women Plus Size'
    sizeGroup = sizeGroup.title()
    if type(CorewordStr)!=str:
        print(f'CorewordStr is not string. code={CorewordStr}')
        exit()
    key_word_list = CorewordStr.split(',')
    key_word_list = list(map(lambda x:x.title(), key_word_list))
    adorn_word_list = AdornwordStr.split(',')
    adorn_word_list = list(map(lambda x:x.title(), adorn_word_list))
    itemname_copywriting, item_name = util.alienateItemName(g_brand, sizeGroup, key_word_list, adorn_word_list, g_srcdata_table['spuClothingType'][SPU])
    g_srcdata_table['spuItemNameDic'][SPU] = item_name
    # 创建所有SKU代码
    colorList = ColorStr.split(',')
    sizeList = SizeStr.split(',')
    rowi = 0  # 计数
    for color in colorList:
        # print(f'    {color}')
        for size in sizeList:
            g_srcdata_table['allSPUList'].append(SPU)    # 每个SKU都记录SPU做下标用
            g_srcdata_table['allColorCodeList'].append(color)  #每个SKU都记录ColorCode做下标用
            SKU = f"{SPU}.{color}{size}"
            g_srcdata_table['allSkuList'].append(SKU)
            sizeName = size
            if ('L' not in size) and ('X' in size):
                sizeName = f"{size}L"
            g_srcdata_table['allSizeList'].append(sizeName)
            g_srcdata_table['allColorList'].append(cp.getVal(color, 'name'))
            g_srcdata_table['allColorCateList'].append(cp.getVal(color, 'cate'))
            g_srcdata_table['allIsPrimary'].append('No')
        rowi = rowi + 1
    # 修改此SPU下最后1个SKU为primary
    g_srcdata_table['allIsPrimary'].pop()
    g_srcdata_table['allIsPrimary'].append('Yes')
    # Site Description
    material_str = ''
    size_str = ''
    pattern_str = g_srcdata_table['spuPattern'][SPU]
    # 材质成分描述拼接
    fabName = g_srcdata_table['spuFabricMaterialName'][SPU]
    if fabName != 'Nan':
        material_str = fabName
    fabPct = g_srcdata_table['spuFabricMaterialPercentage'][SPU]
    if fabPct != '':
        material_str = f"{fabPct}% {material_str}"
    fabName1 = g_srcdata_table['spuFabricMaterialName1'][SPU]
    fabPct1 = g_srcdata_table['spuFabricMaterialPercentage1'][SPU]
    if fabName1 != 'Nan' and fabPct1 != '':
        material_str = f"{material_str} {fabPct1}% {fabName1}"
    fabName2 = g_srcdata_table['spuFabricMaterialName2'][SPU]
    fabPct2 = g_srcdata_table['spuFabricMaterialPercentage2'][SPU]
    if fabName2 != 'Nan' and fabPct2 != '':
        material_str = f"{material_str} {fabPct2}% {fabName2}"
    # 尺码描述拼接
    size_str = " /".join(sizeList)
    occasion_list = OccasionStr.split(',')  # 穿着场景
    occasion_list = list(map(lambda x:x.title(), occasion_list)) # 对每个词做首字母大写处理
    g_srcdata_table['spuSiteDescription'][SPU] = alienateSitedesc(item_name, itemname_copywriting, material_str, size_str, pattern_str, key_word_list, occasion_list)
    # === 文案组装完毕 ==

''' 图片URL处理
'''
imgSrcPath = f'{g_basedir}/imgurl.xlsx'
imgWB = openpyxl.load_workbook(imgSrcPath)
imgWS = imgWB.active
# 遍历取值
g_srcdata_table['skuImgList'] = {}
g_srcdata_table['skuSwatchImgList'] = {}
row_iter = imgWS.iter_rows(values_only=True)
# print(row_iter)
for row in row_iter:
    sku = row[2]
    url = row[4]
    spu, color, order_or_size = parseImageFileName(sku)
    full_spu = f'{g_country}{util.BRAND_TO_STORE_CODE[g_brand]}|{spu}' # 加上区域品牌前缀
    #print(spu, color, order_or_size)
    spucolor = f"{full_spu}_{color}"
    if int(order_or_size)==-1:
        g_srcdata_table['skuSwatchImgList'][spucolor] = url
    else:
        if spucolor not in g_srcdata_table['skuImgList']:
            g_srcdata_table['skuImgList'][spucolor] = [(int(order_or_size), url)]
        else:
            g_srcdata_table['skuImgList'][spucolor].append((int(order_or_size), url))
# 对主图排序
for k,v in g_srcdata_table['skuImgList'].items():
    v.sort(key=lambda x:x[0])


# ============ 开始写数据 ==========================
'''测试数据
for i in range(len(allSkuList)):
    print(f"{allSkuList[i]}  {allSizeList[i]}   {allColorList[i]}   {allIsPrimary[i]}")
'''
# 区分国家区域店铺，拆分不同的处理逻辑模块
df_out = None
if g_country=='CA':
    import listingGeneraterCA as CAGenerator
    df_out = CAGenerator.gen(g_brand, g_srcdata_table)
elif g_country=='US':
    import listingGeneraterUS as USGenerator
    df_out = USGenerator.gen(g_brand, g_srcdata_table)
else:
    print('Unknown Country Code!')
    exit()

# == 输出 =================================
df_out.to_excel(out_path, index=False)
print('完成!')